﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IBM.Data.DB2;
using System.Configuration;
using System.Collections.Specialized;

namespace CourseManager.Domain.Repositories
{
    public class DatabaseTransac
    {
        public  DataTable executeSelect(string pSqlStatement)
        {
            DB2Connection CN = new DB2Connection(Properties.Settings.Default.ConnectionString);
            DB2Command CMD = new DB2Command();
            DB2DataAdapter Adapter;
            CMD.CommandText = pSqlStatement;
            CMD.Connection = CN;
            Adapter = new DB2DataAdapter();
            Adapter.SelectCommand = CMD;

            DataSet result = new DataSet();

            CN.Open();
            try
            {
               Adapter.Fill(result);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                CN.Close();
            }

            return result.Tables[0];
        }

        public  Boolean executeInsert(string pTableName, NameValueCollection pValues)
        {
            string insertStatement = "INSERT INTO {0} ({1}) VALUES ({2})";
            string fields = "";
            string valuesToInsert = "";
            int result = 0;

            for (int i = 0; i < pValues.Count; i++)
            {
                fields += pValues.GetKey(i);
                valuesToInsert += pValues.Get(i);

                if ((i + 1) < pValues.Count)
                {
                    fields += ", ";
                    valuesToInsert += ", ";
                }
            }

            insertStatement = String.Format(insertStatement, pTableName, fields, valuesToInsert);

            DB2Connection CN = new DB2Connection(Properties.Settings.Default.ConnectionString);
            DB2Command CMD = new DB2Command();
            CMD.CommandText = insertStatement;
            CMD.Connection = CN;

            CN.Open();
            try
            {
                result = CMD.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                CN.Close();
            }

            return (result > 0);
        }


        public Boolean executeDelete(string pTableName, List<string> pWhereCondition)
        {
            DB2Connection CN = new DB2Connection(Properties.Settings.Default.ConnectionString);
            DB2Command CMD = new DB2Command();

            string whereStatement = "";
            string sqlStatement = "";
            int result = 0;

            if (pWhereCondition.Count > 0)
                sqlStatement = "DELETE FROM {0} WHERE {1}";
            else
                sqlStatement = "DELETE FROM {0}";

            whereStatement = String.Join(" and ", pWhereCondition);
            sqlStatement = String.Format(sqlStatement, pTableName, whereStatement);

            CMD.CommandText = sqlStatement;
            CMD.Connection = CN;

            CN.Open();
            try
            {
                result = CMD.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                CN.Close();
            }

            return (result > 0);
        }

        public  Boolean executeUpdate(string pTableName, NameValueCollection pValues, List<string> pWhereCondition)
        {
            DB2Connection CN = new DB2Connection(Properties.Settings.Default.ConnectionString);
            DB2Command CMD = new DB2Command();

            int result = 0;
            string updateStatement;
            string valuesToUpdate = convertNameValue(pValues, "=");
            string whereCondition = String.Join(" and ", pWhereCondition);

            if (whereCondition != "")
                updateStatement = "UPDATE {0} set {1} WHERE {2}";
            else
                updateStatement = "UPDATE {0} set {1}";

            updateStatement = String.Format(updateStatement, pTableName, valuesToUpdate, whereCondition);

            CMD.CommandText = updateStatement;
            CMD.Connection = CN;

            CN.Open();
            try
            {
                result = CMD.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                CN.Close();
            }

            return (result > 0);
        }

        private string convertNameValue(NameValueCollection pCollection, string pSeparator)
        {
            string result = "";
            for (int i = 0; i < pCollection.Count; i++)
            {
                result += pCollection.GetKey(i) + " " + pSeparator + " " + pCollection.Get(i);

                if ((i + 1) < pCollection.Count)
                    result += ", ";
            }

            return result;
        }
    }
}
